There are some people who suspect Erland Sommarskog can stretch time, otherwise there can be no explanation for how he discovers everything that he does about SQL Server.

“Overall, I can’t
say I’m sure that
I understand the
point with the cloud…”

A multi-linguist who can speak German, French, Italian, English and Spanish he is a well known mentor and teacher with all the native curiosity of the best database developers. He was awarded MVP status by Microsoft way back in 2001 for his expertise.

Erland’s aficionados say that his posts and solutions to SQL challenges are always brilliant and full of insights. He works as an independent consultant in SQL with a particular emphasis on T-SQL Programming, query tuning, best practice and configuration management and version control. He lives in Stockholm.

RM:

Erland, can you tell me something about the way you work? Do you live a hurried life and work every day, 7 days a week?

ES:

I work as an independent consultant, and I’m more or less tied up full-time with one client. This means that most days I arrive at their office around nine and leave around six.

When I’m at home, I certainly work a lot with SQL Server: answer questions on the public forums to retain my MVP status, work with SQL articles, play with new features or betas, or investigate bugs or funny behaviour in SQL Server. But I do this in the pace I feel like, and I find it an important task at the weekend to recover from a week of work. And, believe or not, I do have engagements outside the SQL Server world as well!

RM:

You’re well known as a mentor and as you know many developers when they start out have other developers they as models. Did you have that support?

ES:

In a way, yes. When I graduated I join a mid-sized consultancy here in Sweden, Enea. It was quite a special place then, as it was owned by the employees, and with the shares fairly evenly distributed, so that not just a few had a majority. I think that is quite unusual for a company of that size.

It was a fantastic environment to absorb things, and my ten years there gave me a strong sense for systems development in the broadest sense. Enea’s focus was on technical real-time systems, but we realised that as information systems were becoming more and more complex, that our knowledge about systems development was applicable there as well. When I joined, Enea had no database tradition, but a number of us – both senior and junior consultants – had an internal study circle where we went through one of Chris Date’s books. And when I left Enea, information systems constituted a substantial part of their business.

When it comes to the database world I’ve followed Usenet newsgroups for areas I have been working with, and I’ve learnt a lot of things that way.

And, I can’t stress this enough the fact I was awarded MVP by Microsoft in 2001 has been tremendously helpful for me. One may think of the MVP award as recognition of expertise, but I certainly did not feel it appropriate to call me an “SQL Server Expert” then; I can only say that was I extremely flattered by being awarded it. The contact with the other SQL Server MVPs and Microsoft developers in our private newsgroup, and also meeting all these bright persons at MVP Summits and PASS, has been very, very rewarding. There is so much I have learnt these last nine years.

RM:

Did anyone ever help with your work as extensively as you have helped others? I mean by criticism and editing work?

ES:

Let’s go back to Enea again. I had been there for a week when I was thrown into a project about writing some supplementary functions for the operating system for the client’s real-time platform. The work as such was a bit dry, or dull if you like. But what was rewarding in this project was the amount of time we spent on reviewing each other’s specifications and code. We also made sure that the right people at the client reviewed our specifications to reach the correct solution. For me as a freshman from the university this was very helpful – but I believe that I also helped to find issues in my colleague’s work. This was a short stint for me, about three months, and since then I have never experienced anything like that in my professional life. There have been times, I’ve taken the time to review SQL code checked in for the system I work with and send out comments to the developers. I know that has been very much appreciated. But let me perfectly honest: reviewing code can be a fairly boring task.

Now, there is one more angle to this question. As you know I have a web site with a couple of longer articles on. Since I publish them on my own web site, there is no formal tech review (or proofreading). Every once in a while I get mail from readers that point out things that are flat wrong, or just grossly unclear. In the latter case, they may not always say this explicitly, but their questions make it apparent that I had not been as clear as I wish to be.

RM:

Do you feel part of a SQL community along with others such as Itzik Ben-Gan?

ES:

The community I most feel part of is SQL Server MVPs, past and present. In the broader sense, it is all the people I meet at PASS, interact with in newsgroups and forums, and who send me mail.

You mention Itzik, and he is a very bright person, and I am glad to be able to count him among my friends. Sometimes I like to think that I can write really clever SQL queries, but then I see the stuff that Itzik and people like Steve Kass (former MVP) or Umachandar Jayachandran (former MVP, now with Microsoft) come up with and I crawl back under my rock.

RM:

What do you think is the best competitive cloud OS solutions from Amazon Google, Salesforce, or IBM? Is there anything in any of their approaches you think any one of the big players would be wise to emulate and/or build on either concept or feature-wise?

ES:

That is a question that I am not competent to answer. I have not paid much attention to Microsoft’s own cloud solution, not even SQL Azure and even less the competitors. Overall, I can’t say I’m sure that I understand the point with the cloud. Yes, I can understand the point for an organisation to outsource running their IT systems. But why would a Swedish company for example put their sensitive data up by some lake in Washington State where a foreign government may decide that they have all rights to get access to that data? It doesn’t seem right to me.

RM:

What are the important parts of your programming toolkit? Do you use source control for instance?

ES:

The most important part is of course Transact-SQL, the language in which I do maybe 90% of my programming. As for the tool of editing my T-SQL files, I use TextPad, a shareware editor. There are several such editors on the market, and I have always felt that they are a better option than the tools that Microsoft has offered. There’s no intellisense in Textpad, only syntax colouring but intellisense is something I have never been very excited about. It gets in the way just as often it is helpful. To load my SQL files, I have access to a toolkit known as AbaPerls that I have developed over the years at the company that I used to work for (and which after an acquisition is my client these days). AbaPerls takes care of some boring work like changing CREATE to ALTER when needed, and it checks that all the tables I refer to actually exist. SQL Server used to do this back in SQL 6.5, but during the development of SQL 7 someone had a brain melt, and it no longer has this function.

I’m a strong believer in source control. It’s funny, you often see people on the newsgroups ask how to version-control SQL code, and my answer is always the same: just do it. I think the problem is that people who use SSMS and similar tools are lead astray by the fact that it is so easy to create an object in the database, that they forget saving it to a file.

You check out the file, you edit it, you save it to disk to compile it (this can be a single keystroke in TextPad), you test, correct errors, and when you are satisfied, you check in. And you know what you have on disk, is what you actually tested. (With tools like SSMS it’s easy to forget to save the last changes.) And what’s in the database, it’s akin to a binary file.

RM:

What tools are in your debugging kit? Do you ever step through code just as a way of checking it when you’re not tracking down a specific bug?

ES:

I use debuggers sparingly, I mainly use them when I work in Perl, my favourite language outside SQL Server. There is a debugger for T-SQL, but unless you have the database on your own machine, there is a lot of red tape to get through to get it to work. And due to the nature of T-SQL, there is a lot you can’t do from it. Sure, you can view the value of variables but you cannot view the contents of temp tables or table variables, and this is usually where the interesting parts are.

So most of the time, my debugging aids are SELECT or PRINT statements. Sometimes added ad hoc as needed, sometimes added in advance to be activated with a @debug = 1 in a call. Or I add log tables and similar things to see what is going on.

Sometimes I try to use Profiler to track down what a piece of code is up to, but usually I get back too much information for it to be useful.

RM:

How about other debugging techniques, such as assertions, or proofs? Do you use any of these? Do you think in terms of variants?

ES:

I use assertions in various forms a lot. I think it is a virtue for a programmer to be a little paranoid. When I design a table, I try to understand what my assumptions are, and I encode them in constraints if possible, else in trigger code. If I write a stored procedure that accepts some input, I can spend over 100 lines of code to validate that input against my assumptions.

Later, when testing, it may prove that some of my assumptions were incorrect, and that particular check should be removed. But that also means that I need to review my code, because the code is not likely to handle the condition that I though could not occur.

RM:

That leads me to another topic, maintaining software. How do you tackle understanding a piece of code that you didn’t write? Where do you start? Page one and read linearly?

ES:

Yes, that can certainly be a daunting task. Where I start depends on the size of the problem. It’s certainly tempting to jump into the middle where you think where the bug is and sometimes that works. But you may also find that your change did not play well with the rest of the code; maybe because the programmer used what I thought was a bug for some special tweak later on.

If I have to understand it all, I often find myself reformatting the code. I have always said that ‘everyone else writes ugly code, it’s only me who writes readable code’ and added that this something that every programmer can subscribe to. We all have our own style.

One immediate benefit of the reformatting exercise is that I can read the code without being irritated by the original programmer’s style. But there is also a second benefit: by going through all the code in this manner, I also go through the first stage of digesting the beast.

Then there is the more drastic approach. Write off the old as beyond hope of any repair and this is what has occupied me for the last couple of months. There is a complex calculation in the system I work with, written and maintained for a number of years by someone who used to work at the company.

His coding style was, well, a little strange. When we realised that were lots of cases he code did not handle, another colleague and I took a bit of time to understand what changes we had to make, but eventually I gave up, and re-implemented the calculation from scratch. Of course, this is nothing to recommend in general, but when code has been around for a long time, the original spirit may have disappeared in the incremental changes over the year. Not to forget that as SQL Server has evolved, better solutions may have become available.

RM:

Is programming getting easier do you think? In the future will more people be able to do something that we would recognise as programming?

ES:

That’s a difficult question for me to answer, since living in the T-SQL world, I don’t feel like I live in the middle of the development of programming languages. T-SQL as a programming in language is not very sophisticated. There are a lot of things that you would take for granted in any modern language that just isn’t there and I think that Microsoft needs to do quite some work in this area.

Then again, many of the things that I feel are missing are tools for the professional programmer, who understands to generalise his code, and not everything may be helpful for the self-taught programmer who learns as he moves along. And SQL is interesting in this sense. I have several colleagues with no formal training in programming that write SQL queries all day long. Granted, they don’t write 50-line queries with row_number and nested CTEs, but they are able to retrieve information. I find it difficult that these persons would have been to get this information on their own if they would have to write in C# or Visual Basic.

RM:

What do you to relax? Do you play sports such as football?

ES:

Not in general, and normally I just take time to read to read about the games in the newspapers. But there is one thing coming up that will keep me busy in the next few weeks: the World Cup. Normally, I hardly watch TV at all, but I usually try to see as many games from the World Cup as possible. I really find it fascinating to see the best players play and there is a certain charm: the difference between the teams is small enough that about any team can win over another. There are often surprise teams that go as far as the semifinals. Still when it comes to the final: it has only happened twice that the World Champions have been a team that neither has won the Cup before. Last time it happened was in 1958. Same goes for the runners-up. When Sweden came third in the World Cup 1994, it was about our biggest sports achievement for a long time.

I also play bridge and in summertime I enjoy going for excursions in the Stockholm area, where there is an abundance of forest and lakes. I also like travelling, and I usually go away some place every year. This year, I spend two lazy weeks in France around Easter. Finally, I have long had a love for music, and I have acquired quite a collection over the years. And recently I’ve been giving it extra attention, as I moved over all my albums to digital format.